package jw3.c2.controller;

import jw3.c2.model.Customer;
import jw3.c2.utils.DBConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

public class CustomerController {
    public static Vector<Vector<Object>> getAll(String U_Name){
        String sql;
        if (U_Name.equals("")){//全查
            sql="select U_ID, U_Name,U_Sex,U_D,U_S,U_A from customer";
        }else {//模糊查
            sql="select U_ID, U_Name,U_Sex,U_D,U_S,U_A from customer where U_Name like ?";
        }

        //1、构造Vector类型用于赋值给表格
        Vector<Vector<Object>> data=new Vector<Vector<Object>>();
        try {
            Connection conn= DBConnection.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql);
            if (!U_Name.equals("")){ //参数化sql语句只在模糊查时进行
                ps.setString(1,"%"+U_Name+"%");
            }

            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                //创造对象，每次放一条数据
//                UserInfo ui=new UserInfo();
                Vector<Object> ui=new Vector<Object>();

                //将每个字段放入对象
                ui.add(rs.getInt("U_ID"));
                ui.add(rs.getString("U_Name"));
                ui.add(rs.getString("U_Sex"));
                ui.add(rs.getString("U_D"));
                ui.add(rs.getString("U_S"));
                ui.add(rs.getString("U_A"));
                //每次将一条数据加入到集合
                data.add(ui);
            }
            DBConnection.close(rs,ps,conn);
            return data;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    public static Vector<Vector<Object>> getAll1(String U_Name){
        String sql;
        if (U_Name.equals("")){//全查
            sql="select U_ID,Vip_ID,U_Name,U_Sex,U_D,U_Phone,U_A from customer";
        }else {//模糊查
            sql="select U_ID,Vip_ID,U_Name,U_Sex,U_D,U_Phone,U_A from customer where U_Name like ?";
        }

        //1、构造Vector类型用于赋值给表格
        Vector<Vector<Object>> data=new Vector<Vector<Object>>();
        try {
            Connection conn= DBConnection.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql);
            if (!U_Name.equals("")){ //参数化sql语句只在模糊查时进行
                ps.setString(1,"%"+U_Name+"%");
            }

            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                //创造对象，每次放一条数据
//                UserInfo ui=new UserInfo();
                Vector<Object> ui=new Vector<Object>();

                //将每个字段放入对象
                ui.add(rs.getInt("U_ID"));
                ui.add(rs.getInt("Vip_ID"));
                ui.add(rs.getString("U_Name"));
                ui.add(rs.getString("U_Sex"));
                ui.add(rs.getString("U_D"));
                ui.add(rs.getString("U_Phone"));
                ui.add(rs.getString("U_A"));
                //每次将一条数据加入到集合
                data.add(ui);
            }
            DBConnection.close(rs,ps,conn);
            return data;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    public static Vector<Vector<Object>> getAll2(String Vip_ID){
        String sql;
        if (Vip_ID.equals("")){//全查
            sql="select U_ID,Vip_ID,U_Name,U_Sex,U_D,U_Phone,U_A from customer";
        }else {//模糊查
            sql="select U_ID,Vip_ID,U_Name,U_Sex,U_D,U_Phone,U_A from customer where Vip_ID like ?";
        }

        //1、构造Vector类型用于赋值给表格
        Vector<Vector<Object>> data=new Vector<Vector<Object>>();
        try {
            Connection conn= DBConnection.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql);
            if (!Vip_ID.equals("")){ //参数化sql语句只在模糊查时进行
                ps.setString(1,Vip_ID);
            }

            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                //创造对象，每次放一条数据
//                UserInfo ui=new UserInfo();
                Vector<Object> ui=new Vector<Object>();

                //将每个字段放入对象
                ui.add(rs.getInt("U_ID"));
                ui.add(rs.getInt("Vip_ID"));
                ui.add(rs.getString("U_Name"));
                ui.add(rs.getString("U_Sex"));
                ui.add(rs.getString("U_D"));
                ui.add(rs.getString("U_Phone"));
                ui.add(rs.getString("U_A"));
                //每次将一条数据加入到集合
                data.add(ui);
            }
            DBConnection.close(rs,ps,conn);
            return data;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    public static boolean logOut(int U_ID){
        String sql="delete from customer where U_ID=?";
        return DBConnection.zsg(sql,U_ID);
    }
    public static boolean register(Customer ui){
        String sql="insert customer (U_ID,Vip_ID,U_Name,U_Sex,U_D,U_Phone,U_A) values(?,?,?,?,?,?,?)";
        return DBConnection.zsg
                (sql,ui.getU_ID(),ui.getVip_ID(),ui.getU_Name(),ui.getU_Sex(),ui.getU_D(),ui.getU_Phone(),ui.getU_A());
    }
    public static boolean edit(Customer ui){
        String sql="update customer set Vip_ID=?,U_Name=?,U_Sex=?,U_D=?,U_Phone=?,U_A=? where U_ID=?";
        return DBConnection.zsg(sql,ui.getVip_ID(),ui.getU_Name(),ui.getU_Sex(),ui.getU_D(),ui.getU_Phone(),ui.getU_A(),ui.getU_ID());
    }
}

